#!/bin/bash

#判断是否为空值
if [ -n "$1" ] ; then
  data_date=$1
else
  data_date=`date -d '-1 days' +%F`
fi


DWS_HOUR_COMMON_ADS_AGG_SQL="
INSERT OVERWRITE TABLE jtp_ads_warehouse.dws_hour_common_ads_agg PARTITION (dt='${data_date}')
SELECT
    hour(from_unixtime(event_time/1000)) AS hr
    ,ad_id
    ,ads_name
    ,platform_id
    ,platform_name_zh
    ,client_province
    ,client_city
    ,client_os_type
    ,client_browser_type
    ,is_invalid_traffic
    ,count(if(event_type='click',ad_id,NULL))AS click_count
    ,count(ad_id) AS impression_count
FROM jtp_ads_warehouse.dwd_ads_event_log_inc
WHERE dt='${data_date}'
AND event_type IN ('click','impression')
GROUP BY hour(from_unixtime(event_time/1000))
       ,ad_id
       ,ads_name
       ,platform_id
       ,platform_name_zh
       ,client_province
       ,client_city
       ,client_os_type
       ,client_browser_type
       ,is_invalid_traffic
;
"

/opt/module/spark/bin/beeline -u jdbc:hive2://node101:10001 -n bwie -e "${DWS_HOUR_COMMON_ADS_AGG_SQL}"






